package cn.biwysh.exam.util;

import java.sql.*;
import java.util.Vector;

import javax.sql.*;
import javax.naming.*;

public class DBUtil 
{
	private boolean debug = true;
	private static DBUtil instance = null;
	private DataSource g_ds = null;
	
	private DBUtil ()
	{
		lookup();
		Recorder.log.info("new DBUtil() excuted!");
	}
	private void lookup ()
	{
		Context ctx = null;
		try 
		{
			ctx = new InitialContext();
			g_ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysql");
		}
		catch (NamingException e) 
		{
			if(debug) {
				e.printStackTrace();
			}
		}
		Recorder.log.info("lookup() excuted!");
	}
	public static DBUtil getInstance ()
	{
		if (instance == null)
		{
			instance = new DBUtil();
		}
		return instance;
	}
	
	public Vector excuteSQL (String sql)
	{
		Vector vr = new Vector();
		Vector vc = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		ResultSetMetaData rsmd = null;
		Object obj = null;
		try 
		{
			if (g_ds == null)
				lookup();
			conn = g_ds.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			rsmd = rs.getMetaData();
			int columnNum = rsmd.getColumnCount();
			while (rs.next())
			{
				vc = new Vector();
				for (int i = 0; i < columnNum; i++)
				{
					obj = rs.getObject(i+1);
					vc.add(obj);
				}
				vr.add(vc);
			}
		} 
		catch (SQLException e) 
		{
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error(e);
		} 
		finally
		{
			
			try 
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				if(debug) {
					e.printStackTrace();
				}
				Recorder.log.error("excuteSQL()--->"+e);
			}
			
		}
		return vr;
	}
	
	public int updateAndBackGenerateId (String sql)
	{
		int record = -1;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try
		{
			if (g_ds == null)
				lookup();
			conn = g_ds.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
			rs = stmt.getGeneratedKeys();
			rs.next();
			record = rs.getInt(1);
		}
		catch (SQLException e)
		{
			if(debug) {
				e.printStackTrace();
			}
			record = -1;
			Recorder.log.error("updateAndBackGenerateId (String sql)--->"+e);
		}
		finally
		{
			try
			{
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				if(debug) {
					e.printStackTrace();
				}
				Recorder.log.error("updateAndBackGenerateId (String sql)--->"+e);
			}
		}
		return record;
	}
	
	public Connection getConnection() {
		Connection conn = null;
		try {
			if(g_ds == null) lookup();
			conn = g_ds.getConnection();
		} catch (SQLException e) {
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error("getConnection ()--->"+e);
		}
		return conn;
	}
	
	public void close(Connection conn) {
		try {
			if(conn!=null) {
				conn.close();
			}
		} catch (SQLException e) {
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error("close (Connection conn)--->"+e);
		}
	}
	
	public void close(PreparedStatement ps) {
		try {
			if(ps!=null) {
				ps.close();
			}
		} catch (SQLException e) {
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error("close (PreparedStatement ps)--->"+e);
		}
	}
	
	public void close(ResultSet rs) {
		try {
			if(rs!=null) {
				rs.close();
			}
		} catch (SQLException e) {
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error("close (ResultSet rs)--->"+e);
		}
	}
	
	public void close(Statement st) {
		try {
			if(st!=null) {
				st.close();
			}
		} catch (SQLException e) {
			if(debug) {
				e.printStackTrace();
			}
			Recorder.log.error("close (Statement st)--->"+e);
		}
	}
	
	public void close(ResultSet rs,PreparedStatement ps, Statement st, Connection conn) {
		this.close(rs);
		this.close(ps);
		this.close(st);
		this.close(conn);
	}
	
	public int updateSQL (String sql)
	{
		int record = -1;
		Connection conn = null;
		Statement stmt = null;
		try
		{
			if (g_ds == null)
				lookup();
			conn = g_ds.getConnection();
			stmt = conn.createStatement();
			record = stmt.executeUpdate(sql);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			Recorder.log.error("updateSQL (String sql)--->"+e);
		}
		finally
		{
			try
			{
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			}
			catch (SQLException e)
			{
				if(debug) {
					e.printStackTrace();
				}
				Recorder.log.error("updateSQL (String sql)--->"+e);
			}
		}
		return record;
	}

}
